# generic modules
import sqlite3
from decimal import Decimal, ROUND_HALF_UP
#from collections import OrderedDict
from ordereddict import OrderedDict
# fressen modules


# SQL related stuff
MLTdb = sqlite3.connect('mlt_db.sqlite', check_same_thread = False)
MLTdb.row_factory = sqlite3.Row
# MLTrow = ['Type_MLT', 'Family', 'Type', 'Sex','E0','age','m','q','l_','L','d','s','T','E','a']

# I want to keep this dynamic, but why is that double 'for' so ugly?
def tableType():
    c = MLTdb.cursor()
    tabletype = []
    # As we calculate L, T and C according to Coale & Demeny's algorithms, we stick to the CD* tables for now
    tabletypes = c.execute('select Type from mlt_db where Type GLOB "CD*" group by Type')
    for ttrow in tabletypes:
        for types in ttrow:
            tabletype.append(types)
    return tabletype
    c.close()
def E0values():
    c = MLTdb.cursor()
    E0value = []
    E0values = c.execute('select E0 from mlt_db group by E0')
    for e0row in E0values:
        for e0values in e0row:
            E0value.append(str(e0values))
    return E0value
    c.close()

#def calculateC(t):
#    c = MLTdb.cursor()
    
def calculateC(t):
     c = MLTdb.cursor()
     Cvalues = c.execute('select age,L,T from mlt_db where age <= "100" and Type = ? and Sex = ? and E0 = ?', t)
     firstrow = c.fetchone()
     T0 = firstrow[2]
     Cvalue = OrderedDict()
     for crow in Cvalues:
         if crow[2] != 0:
             C = 100 * (crow[1] / T0)
             Cvalue[str(crow[0])] = str(C)
     return Cvalue
# This one is going to be a little harder. We first need to calculate k values, which is based on values of q. Jikes:

def calculateL(t):

    c = MLTdb.cursor()
    # calculate L is done a number of different ways, depending on the years lived.
    # First thing we need to do, is match the actual CD symbols to the ones we use here 

    #1L0 = k0l0 + (1 - k0)l1 
	#calculate L0: L0 = a0*l0 + (1-a0)l1
    
#    L0value = {}
#    Lvalue = []
#    L0values = c.execute('select age,l_,a from mlt_db where age <= "1" and Type = ? and Sex = ? and E0 = ?', t)
#    for l0row in L0values:
#        L0value[str(l0row[0])] = [str(l0row[1]),l0row[2]]
#    a0 = Decimal(L0value['0'][1].replace(",","."))
#    l0 = int(L0value['0'][0])
#    l1 = int(L0value['1'][0])
#    L0 = a0*l0 + (1-a0)*l1
#    L0 = round(Decimal(L0, ROUND_HALF_UP))
#       # for l0values in l0row:
#       #     l0values = str(l0values)
#       #     L0value.append(l0values)
#    #return L0
#    Lvalues = c.execute('select age,l_,a from mlt_db where age >= "1" and age <= "100" and Type = ? and Sex = ? and E0 = ?', t)
#    firstrow = c.fetchone()
#    firstvalue = firstrow[1]
#    for lrow in Lvalues:
#        lvalue = (firstvalue + lrow[1])/2 * 4
#        Lvalue.append(str(lvalue)) 
#        firstvalue = lrow[1]
#        #Lvalue[str(lrow[0])] = [str(lrow[1]),lrow[2]]
#    c.close()
#    return Lvalue 

def getPyramid(t, cohort, femratio):
    # very basic way of reading the database and using it as input
    output = {}
    categories = []
    femdata = []
    maledata = []
    maleratio = 100 - femratio
    # has been secMod'ed in fressen.py 
    cohort = int(cohort)

    cursor = MLTdb.cursor()
    #for now need to provide Type, Sex and E0
        # XXX surprisingly, for age 100 and up my calculations and the actual data in the tables diverge.
        # Most likely, to extend the tables to 130, a different calculation was made. (Brass Logit/ m50?)
        # For now I'm just going to be rude (as the UN guys are themselves in the spreadsheet) and throw away
        # everything above age 100.
    cursor.execute('select age,q from mlt_db where age <= "100" and Type = ? and Sex = ? and E0 = ?', t)
    results = cursor.fetchall()
    
    for row in results:
            categories.append(str(row['age']))
            # just before we output we turn the float into a string to make it at Decimal (yuck!)
            femdata.append(round(Decimal(str(cohort/100 * femratio), ROUND_HALF_UP)))
            maledata.append(round(Decimal(str(cohort/100 * maleratio * -1), ROUND_HALF_UP)))
            result = {'age': row['age'], 'q' : row['q'], 'cohortsize': cohort}

            q = row['q'].replace(",",".")
            d = cohort*float(q)
            cohort = cohort-d
    output['categories'] = categories
    output['femdata'] = femdata
    output['maledata'] = maledata
    return output 

